﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Collections;
using Maticsoft.DBUtility;
using System.Data.SqlClient;

namespace DAL.wlDAL
{
    public class DAL_WebExample
    {
        /// <summary>
        /// 根据特定条件查商品信息
        /// </summary>
        /// <param name="htwhere">Hashtable</param>
        /// <returns>DataTable</returns>
        public DataTable GetWebExampleInfo(Hashtable htwhere)
        {
            string sql = @" select Fct_WebExample.*,Fct_Category.CategoryID,Fct_Category.CategoryName,Fct_Category.PriorId from Fct_WebExample 
	                        inner join Rel_WebCategory on Rel_WebCategory.WebExampleID=Fct_WebExample.WebExampleID and Rel_WebCategory.Disabled=0
	                        inner join Fct_Category on Rel_WebCategory.CategoryID=Fct_Category.CategoryID and Fct_Category.Disabled=0
	                        left join Rel_WebPicture on Rel_WebPicture.WebExampleID=Fct_WebExample.WebExampleID and Rel_WebCategory.Disabled=0
	                        and Rel_WebPicture.Sort=1
	                        where Fct_WebExample.Disabled=0 and Fct_WebExample.WebState=0
	                        and Fct_Category.Grade=0 and Fct_Category.IsDisplay=0 ";
            if (htwhere.ContainsKey("CategoryID"))
            {
                sql += " and Fct_Category.CategoryID='" + htwhere["CategoryID"].ToString() + "' ";
                sql += " order by Fct_Category.Sort asc";
            }
            else
            {

                sql += " order by Fct_WebExample.REC_CreateTime desc";
            }
            DataTable dttaocan = DbHelperSQL.Query(sql).Tables[0];
            return dttaocan;
            //and Fct_OtherCategory.OtherCategoryGroup='IT'
            // order by Fct_OtherCategory.Sort asc";
        }

        /// <summary>
        /// 添加/修改 网站实例
        /// </summary>
        /// <param name="mWebPic"></param>
        /// <param name="type">0 添加 1 修改 2 删除</param>
        /// <returns></returns>
        public int AddWebExample(WLong.Model.Rel_WebPicture mWebPic, int type)
        {
            string strSql = null;
            SqlParameter[] para = null;
            if (type==0)
            {
                strSql = @"insert into Rel_WebPicture (WebPictureID,WebExampleID,PictureName,Path,Sort,BinaryData,Disabled,REC_CreateTime,REC_CreateBy) 
                values (@WebPictureID,@WebExampleID,@PictureName,@Path,@Sort,@BinaryData,@Disabled,@REC_CreateTime,@REC_CreateBy)";

                para = new SqlParameter[9];
                para[0] = new SqlParameter("@WebPictureID", mWebPic.WebPictureID);
                para[1] = new SqlParameter("@WebExampleID", mWebPic.WebExampleID);
                para[2] = new SqlParameter("@PictureName", mWebPic.PictureName);
                para[3] = new SqlParameter("@Path", mWebPic.Path);
                para[4] = new SqlParameter("@Sort", mWebPic.Sort);
                para[5] = new SqlParameter("@Disabled", mWebPic.Disabled);
                para[6] = new SqlParameter("@REC_CreateTime", mWebPic.REC_CreateTime);
                para[7] = new SqlParameter("@REC_CreateBy", mWebPic.REC_CreateBy);
                para[8] = new SqlParameter("@BinaryData", mWebPic.BinaryData);
            }
            else if(type == 1)
            {
                strSql = @"update Rel_WebPicture set PictureName=@PictureName,Path=@Path,Sort=@Sort,BinaryData=@BinaryData,REC_ModifyTime=@REC_ModifyTime,REC_ModifyBy=@REC_ModifyBy 
                where WebPictureID=@WebPictureID";

                para = new SqlParameter[7];
                para[0] = new SqlParameter("@PictureName", mWebPic.PictureName);
                para[1] = new SqlParameter("@Path", mWebPic.Path);
                para[2] = new SqlParameter("@Sort", mWebPic.Sort);
                para[3] = new SqlParameter("@BinaryData", mWebPic.BinaryData);
                para[4] = new SqlParameter("@REC_ModifyTime", mWebPic.REC_CreateTime);
                para[5] = new SqlParameter("@REC_ModifyBy", mWebPic.REC_CreateBy);
                para[6] = new SqlParameter("@WebPictureID", mWebPic.WebPictureID);
                
            }
            else if (type == 2)
            {
                strSql = @"update Rel_WebPicture set Disabled=1, REC_ModifyTime=@REC_ModifyTime,REC_ModifyBy=@REC_ModifyBy where WebPictureID=@WebPictureID";

                para = new SqlParameter[3];
                para[0] = new SqlParameter("@WebPictureID", mWebPic.WebPictureID);
                para[1] = new SqlParameter("@REC_ModifyTime", mWebPic.REC_CreateTime);
                para[2] = new SqlParameter("@REC_ModifyBy", mWebPic.REC_CreateBy);
            }
            return DbHelperSQL.ExecuteSql(strSql, para);
        }

        /// <summary>
        /// 根据特定条件查信息OtherCategoryGroup或OtherCategoryID
        /// </summary>
        /// <param name="htwhere">Hashtable</param>
        /// <returns>DataTable</returns>
        public DataTable GetTopActicleInfo(int Top, Hashtable htwhere)
        {
            string sql = @" select";
            if (Top != 0)
            {
                sql += " top " + Top.ToString() + " ";
            }
            sql += @" Fct_ArticleInfomation.*,Fct_OtherCategory.OtherCategoryName,Fct_OtherCategory.OtherCategoryGroup,Fct_OtherCategory.OtherCategoryID,Fct_ArticleInfomation.REC_CreateTime  
                            from Fct_OtherCategory
                                inner join Rel_ArticleOtherCategory on Fct_OtherCategory.OtherCategoryID=Rel_ArticleOtherCategory.OtherCategoryID and Rel_ArticleOtherCategory.Disabled=0
                                inner join Fct_ArticleInfomation on Rel_ArticleOtherCategory.ArticleInfomationID=Fct_ArticleInfomation.ArticleID and Fct_ArticleInfomation.Disabled=0 
                                where Fct_OtherCategory.Disabled=0 ";
            if (htwhere.ContainsKey("OtherCategoryGroup"))
            {
                sql += " and Fct_OtherCategory.OtherCategoryGroup='" + htwhere["OtherCategoryGroup"].ToString() + "' ";
            }
            if (htwhere.ContainsKey("OtherCategoryID"))
            {
                sql += " and Fct_OtherCategory.OtherCategoryID='" + htwhere["OtherCategoryID"].ToString() + "' ";
            }
            sql += " order by Fct_ArticleInfomation.REC_CreateTime desc ";
            DataTable dttaocan = DbHelperSQL.Query(sql).Tables[0];
            return dttaocan;
        }

        /// <summary>
        /// 根据特定条件查商品信息
        /// </summary>
        /// <param name="htwhere">Hashtable</param>
        /// <returns>DataTable</returns>
        public DataTable GetTopWebExampleInfo(int Top, Hashtable htwhere)
        {
            string sql = @" select";
            if (Top != 0)
            {
                sql += " top " + Top.ToString();
            }
            sql += @" Fct_WebExample.*,Fct_Category.CategoryID,Fct_Category.CategoryName,Fct_Category.PriorId from Fct_WebExample 
	                        inner join Rel_WebCategory on Rel_WebCategory.WebExampleID=Fct_WebExample.WebExampleID and Rel_WebCategory.Disabled=0
	                        inner join Fct_Category on Rel_WebCategory.CategoryID=Fct_Category.CategoryID and Fct_Category.Disabled=0
	                        left join Rel_WebPicture on Rel_WebPicture.WebExampleID=Fct_WebExample.WebExampleID and Rel_WebCategory.Disabled=0
	                        and Rel_WebPicture.Sort=1
	                        where Fct_WebExample.Disabled=0 and Fct_WebExample.WebState=0
	                        and Fct_Category.Grade=0 and Fct_Category.IsDisplay=0 ";
            if (htwhere.ContainsKey("CategoryID"))
            {
                sql += " and Fct_Category.CategoryID='" + htwhere["CategoryID"].ToString() + "' ";
                sql += " order by Fct_Category.Sort asc";
            }
            else
            {

                sql += " order by Fct_WebExample.REC_CreateTime desc";
            }
            DataTable dttaocan = DbHelperSQL.Query(sql).Tables[0];
            return dttaocan;
            //and Fct_OtherCategory.OtherCategoryGroup='IT'
            // order by Fct_OtherCategory.Sort asc";
        }

        /// <summary>
        /// 用户留言功能
        /// </summary>
        /// <param name="message">WLong.Model.CustomerMessage</param>
        /// <returns>bool</returns>
        public bool AddCustomerMessage(WLong.Model.CustomerMessage message)
        {
            string strsql = @" insert into Fct_CustomerMessage(CustomerMessageID,CustomerName,CustomerMobile,CustomerTelephone,CustomerEmail,
	                        CustomerContent,Disabled,REC_CreateTime,REC_CreateBy)
	                        values(@CustomerMessageID,@CustomerName,@CustomerMobile,@CustomerTelephone,@CustomerEmail,
	                        @CustomerContent,@Disabled,@REC_CreateTime,@REC_CreateBy)";
            SqlParameter[] parm ={
                                    new SqlParameter("@CustomerMessageID",message.CustomerMessageID),
                                    new SqlParameter("@CustomerName",message.CustomerName),
                                    new SqlParameter("@CustomerMobile",message.CustomerMobile),
                                    new SqlParameter("@CustomerTelephone",message.CustomerTelephone),
                                    new SqlParameter("@CustomerEmail",message.CustomerEmail),
                                    new SqlParameter("@CustomerContent",message.CustomerContent),
                                    new SqlParameter("@Disabled",message.Disabled),
                                    new SqlParameter("@REC_CreateTime",message.REC_CreateTime),
                                    new SqlParameter("@REC_CreateBy",message.REC_ModifyBy)
                                };
            int revalue = DbHelperSQL.ExecuteSql(strsql, parm);
            if (revalue > 0)
            {
                return true;
            }
            return false;
        }
    }
}
